﻿using System.Diagnostics;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;
using System.Windows.Forms;

namespace BierdopjeTV.API
{
    class BierdopjeShows
    {
        
        
        #region Fields

        private string m_ShowName;
        private int m_ShowID;
        private int m_Seasons;
        private int m_Episodes;
        private string m_ShowStatus;
        private string m_Network;
        private string m_AirTime;
        private string m_Summary;

        private int m_TVDBID;
        private string m_FirstAired;
        private string m_LastAired;
        private string m_NextEpisode;
        private double m_Score;
        private int m_Runtime;
        private string m_HasTranslators;
        private int m_Favorites;
        


        #endregion


        #region Properties



        public string ShowName
        {
            get { return m_ShowName; }
            set { m_ShowName = value; }
        }


        public int ShowID
        {
            get { return m_ShowID; }
            set { m_ShowID = value; }
        }


        public int Seasons
        {
            get { return m_Seasons; }
            set { m_Seasons = value; }
        }

        public int Episodes
        {
            get { return m_Episodes; }
            set { m_Episodes = value; }
        }

        public string ShowStatus
        {
            get { return m_ShowStatus; }
            set { m_ShowStatus = value; }
        }

        public string Network
        {
            get { return m_Network; }
            set { m_Network = value; }
        }


        public string AirTime
        {
            get { return m_AirTime; }
            set { m_AirTime = value; }
        }
        public int TVDBID
        {
            get { return m_TVDBID; }
            set { m_TVDBID = value; }
        }
        public string FirstAired
        {
            get { return m_FirstAired; }
            set { m_FirstAired = value; }
        }
        public string NextEpisode
        {
            get { return m_NextEpisode; }
            set { m_NextEpisode = value; }
        }
        public double Score
        {
            get { return m_Score; }
            set { m_Score = value; }
        }
        public int Runtime
        {
            get { return m_Runtime; }
            set { m_Runtime = value; }
        }
        public string HasTranslators
        {
            get { return m_HasTranslators; }
            set { m_HasTranslators = value; }
        }
        public string LastAired
        {
            get { return m_LastAired; }
            set { m_LastAired = value; }
        }
        public int Favorites
        {
            get { return m_Favorites; }
            set { m_Favorites = value; }
        }   

        public string Summary
        {
            get { return m_Summary; }
            set { m_Summary = value; }
        }

        #endregion


        #region Methods
        /// <summary>
        /// Find an alternative name for the source string        
        /// </summary>
        /// <param name="source">parsed showname</param>
        /// <returns>a replacement string if found</returns>
        public static string GetReplaceMent(string source)
        {            
            SQLiteResultSet _Results;

            _Results = Helpers.SqliteHelper.ExecuteQuery("SELECT ShowNameNew FROM Replacements WHERE ShowNameOld = \'" + source + "\'");

            int records = _Results.Rows.Count;

            if (records > 0) {
                return _Results.Rows[0].fields[0];
             }
            else
            {
                return source;
            }
        }
        /// <summary>
        /// Get ShowId from the database
        /// </summary>
        /// <param name="ShowName">Source Showname</param>
        /// <returns>0 when not found, showid if found.</returns>
        public static int getShowID(string ShowName)
        {

            SQLiteResultSet _Results;
            string _Query;
            _Query = "SELECT ShowID FROM BierdopjeShows WHERE Showname = \'" + GetReplaceMent(ShowName) + "\'";
            _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);
            
            int records = _Results.Rows.Count;

            if (records > 0) {
                return Convert.ToInt32(_Results.Rows[0].fields[0].ToString());
             }
            else //Try to add the show!
            {
                APIFunctions _APIFunctions = new API.APIFunctions();
                List<BierdopjeShows> ApiShow = _APIFunctions.FindShowByName(ShowName);
                if (ApiShow.Count == 1)
                {
                    if (ShowName != ApiShow[0].ShowName)
                    {
                        addReplacement(ShowName, ApiShow[0].ShowName);
                    }
                    return addShow(ApiShow[0]);
                }
                else
                {

                    Configuration.frmZoekShow bla = new Configuration.frmZoekShow(ShowName);
                    bla.ShowDialog();
                    if (bla.ShowID > 0)
                    {
                        BierdopjeShows _Show = _APIFunctions.GetShowById(bla.ShowID);
                        if (ShowName != _Show.ShowName)
                        {
                            addReplacement(ShowName, _Show.ShowName);
                        }
                        addShow(_Show);
                    }
                    
                    return bla.ShowID;
                 
                }
               
            }
        }
        /// <summary>
        /// Add an show to the database :-)
        /// </summary>
        /// <param name="_Show"></param>
        private static int addShow(BierdopjeShows _Show)
        {
            string _Query;
            try
            {

                _Query = "";
                _Query += "INSERT INTO BierdopjeSHOWS" + Environment.NewLine;
                _Query += "(" + Environment.NewLine;
                _Query += "	ShowID," + Environment.NewLine;
                _Query += "	TVDBID," + Environment.NewLine;
                _Query += "	ShowName," + Environment.NewLine;
                _Query += "	Seasons," + Environment.NewLine;
                _Query += "	Episodes," + Environment.NewLine;
                _Query += "	FirstAired," + Environment.NewLine;
                _Query += "	LastAired," + Environment.NewLine;
                _Query += "	NextEpisode," + Environment.NewLine;
                _Query += "	RunTime," + Environment.NewLine;
                _Query += "	HasTranslators," + Environment.NewLine;
                _Query += "	ShowStatus," + Environment.NewLine;
                _Query += "	Favorites," + Environment.NewLine;
                _Query += "	Score," + Environment.NewLine;
                _Query += "	Network," + Environment.NewLine;
                _Query += "	AirTime," + Environment.NewLine;
                _Query += "	Summary" + Environment.NewLine;
                _Query += ")" + Environment.NewLine;
                _Query += "VALUES" + Environment.NewLine;
                _Query += "(" + Environment.NewLine;
                _Query += " " + _Show.ShowID + "," + Environment.NewLine;
                _Query += " " + _Show.TVDBID + "," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.ShowName) + "'," + Environment.NewLine;
                _Query += " " + _Show.Seasons + "," + Environment.NewLine;
                _Query += " " + _Show.Episodes + "," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.FirstAired) + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.LastAired) + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.NextEpisode) + "'," + Environment.NewLine;
                _Query += " " + _Show.Runtime + "," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.HasTranslators) + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.ShowStatus) + "'," + Environment.NewLine;
                _Query += " " + _Show.Favorites + "," + Environment.NewLine;
                _Query += " '" + _Show.Score + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.Network) + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.AirTime) + "'," + Environment.NewLine;
                _Query += " '" + dbSave(_Show.Summary) + "'" + Environment.NewLine;
                _Query += ")" + Environment.NewLine;
                Helpers.SqliteHelper.ExecuteQuery(_Query);
            }
            catch { }

            

            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery("SELECT ShowID FROM BierdopjeShows WHERE ShowName = \'" + dbSave(_Show.ShowName) + "\'");
            if (_Results.Rows.Count > 0)
            {
                
                
                return Convert.ToInt32(_Results.Rows[0].fields[0].ToString());

                
            }
            else
            {
                return 0;
            }

        }

        private static string dbSave(string source)
        {
            return source.Replace("'", "''");
        }

        /// <summary>
        /// Add an replacement for an source showname.
        /// </summary>
        /// <param name="ShowNameOld">Name to replace</param>
        /// <param name="ShowNameNew"> with...</param>
        private static void addReplacement(string ShowNameOld, string ShowNameNew)
        {
            string _Query;
            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery("SELECT * FROM ReplaceMents WHERE ShowNameOld = '" + dbSave(ShowNameOld) + "'");
            if (_Results.Rows.Count == 0)
            {
                _Query = "";
                _Query += "INSERT INTO ReplaceMents" + Environment.NewLine;
                _Query += "(" + Environment.NewLine;
                _Query += "	ShowNameOld," + Environment.NewLine;
                _Query += "	ShowNameNew" + Environment.NewLine;
                _Query += ")" + Environment.NewLine;
                _Query += "VALUES" + Environment.NewLine;
                _Query += "(" + Environment.NewLine;
                _Query += "	'" + dbSave(ShowNameOld) + "'," + Environment.NewLine;
                _Query += "	'" + dbSave(ShowNameNew) + "'" +  Environment.NewLine;
                _Query += ")" + Environment.NewLine;
                _Query += "" + Environment.NewLine;
            }
            else
            {
                _Query = "";
                _Query += "UPDATE ReplaceMents SET" + Environment.NewLine;
                _Query += "	ShowNameNew = '" + dbSave(ShowNameNew) + "'" + Environment.NewLine;
                _Query += "WHERE" + Environment.NewLine;
                _Query += "	ShowNameOld = '" + dbSave(ShowNameOld) + "'" + Environment.NewLine;
            }
            Debug.Print(_Query);
            Helpers.SqliteHelper.ExecuteQuery(_Query);
        }

        #endregion




    }
}
